from jobs.sql_mehods import *
from datetime import datetime, timedelta

db = MySQLHelper(host='mmservice-05.mysql.hotgrid.cn', port=3306,
                 user='electricity_api_service', password='GJlfh7&#jg',
                 db='electricity_data', charset='utf8')
# 设置开始时间
start_time = datetime.strptime('2024-01-03 00:00:00', '%Y-%m-%d %H:%M:%S')

# 初始化空的时间列表
time_list_start = []
time_list_end = []

# 生成时间列表
for i in range(24 * 60 // 5):    # 每天有24*60分钟，//5表示每5分钟一个间隔
    time_list_start.append((start_time + timedelta(minutes=i*5)).strftime('%Y-%m-%d %H:%M:%S'))
    time_list_end.append((start_time + timedelta(minutes=(i+1)*5)).strftime('%Y-%m-%d %H:%M:%S'))




# 单词异常断数函数监测
def single_bad_device_warning(current_time,next_time):
    # 一共有多少设备
    sql_all_dev = f"""SELECT b.guid 
             FROM elecdata_basic_info b 
             WHERE b.data_type_id = '8'"""
    data = db.execute_select(sql_all_dev)
    all_dev_len = len(data)

    single_dev_work_sql = f""" SELECT e.guid, count(e.guid) as counts
          FROM elecdata_data_5m_202401 e
          JOIN (
             SELECT b.guid 
             FROM elecdata_basic_info b 
             WHERE b.data_type_id = '8'
          ) guids
          ON e.guid = guids.guid
          WHERE e.data_time >= '{current_time}' 
          AND e.data_time <= '{next_time}'
          GROUP BY guid """
    data2 = db.execute_select(single_dev_work_sql)
    single_dev_work_len = len(data2)

    sql_all_work_sql = f"""SELECT e1.guid,e2.value - e1.value AS diff_value
    FROM 
    (SELECT e.value, e.guid
    FROM elecdata_data_5m_202401 e
    JOIN (
        SELECT b.guid 
        FROM elecdata_basic_info b 
        WHERE b.data_type_id = '8'
    ) guids
    ON e.guid = guids.guid
    WHERE e.data_time = '{current_time}' 
    AND value >=0
    AND e.guid in (
       SELECT guid from (
          SELECT e.guid, count(e.guid) as counts
          FROM elecdata_data_5m_202401 e
          JOIN (
             SELECT b.guid 
             FROM elecdata_basic_info b 
             WHERE b.data_type_id = '8'
          ) guids
          ON e.guid = guids.guid
          WHERE e.data_time >= '{current_time}' 
          AND e.data_time <= '{next_time}'
          GROUP BY guid ) as subquery
       WHERE counts = 2)
    ) e1
    JOIN
    (SELECT e.value, e.guid
    FROM elecdata_data_5m_202401 e
    JOIN (
        SELECT b.guid 
        FROM elecdata_basic_info b 
        WHERE b.data_type_id = '8'
    ) guids
    ON e.guid = guids.guid
    WHERE e.data_time = '{next_time}' 
    AND value >=0
    AND e.guid in (
       SELECT guid from (
          SELECT e.guid, count(e.guid) as counts
          FROM elecdata_data_5m_202401 e
          JOIN (
             SELECT b.guid 
             FROM elecdata_basic_info b 
             WHERE b.data_type_id = '8'
          ) guids
          ON e.guid = guids.guid
          WHERE e.data_time >= '{current_time}' 
          AND e.data_time <= '{next_time}'
          GROUP BY guid ) as subquery
       WHERE counts = 2)
    ) e2
    ON e1.guid = e2.guid
    WHERE e2.value - e1.value >= 0;
    """
    data3 = db.execute_select(sql_all_work_sql)
    all_work_dev_len = len(data3)

    sql_all_unexp_work_sql = f"""SELECT e1.guid,e2.value - e1.value AS diff_value
    FROM 
    (SELECT e.value, e.guid
    FROM elecdata_data_5m_202401 e
    JOIN (
        SELECT b.guid 
        FROM elecdata_basic_info b 
        WHERE b.data_type_id = '8'
    ) guids
    ON e.guid = guids.guid
    WHERE e.data_time = '{current_time}' 
    AND value >=0
    AND e.guid in (
       SELECT guid from (
          SELECT e.guid, count(e.guid) as counts
          FROM elecdata_data_5m_202401 e
          JOIN (
             SELECT b.guid 
             FROM elecdata_basic_info b 
             WHERE b.data_type_id = '8'
          ) guids
          ON e.guid = guids.guid
          WHERE e.data_time >= '{current_time}' 
          AND e.data_time <= '{next_time}'
          GROUP BY guid ) as subquery
       WHERE counts = 2)
    ) e1
    JOIN
    (SELECT e.value, e.guid
    FROM elecdata_data_5m_202401 e
    JOIN (
        SELECT b.guid 
        FROM elecdata_basic_info b 
        WHERE b.data_type_id = '8'
    ) guids
    ON e.guid = guids.guid
    WHERE e.data_time = '{next_time}' 
    AND value >=0
    AND e.guid in (
       SELECT guid from (
          SELECT e.guid, count(e.guid) as counts
          FROM elecdata_data_5m_202401 e
          JOIN (
             SELECT b.guid 
             FROM elecdata_basic_info b 
             WHERE b.data_type_id = '8'
          ) guids
          ON e.guid = guids.guid
          WHERE e.data_time >= '{current_time}' 
          AND e.data_time <= '{next_time}'
          GROUP BY guid ) as subquery
       WHERE counts = 2)
    ) e2
    ON e1.guid = e2.guid
    WHERE e2.value - e1.value <0;
    """
    data4 = db.execute_select(sql_all_unexp_work_sql)
    sql_all_unexp_work_len = len(data4)
    return all_dev_len,single_dev_work_len - all_work_dev_len,all_work_dev_len,round((single_dev_work_len - all_work_dev_len) / all_work_dev_len, 3),sql_all_unexp_work_len,data4


if __name__ == "__main__":
    # 每一次循环则进行一次监测
    for i in range(len(time_list_start) - 1):
        print(time_list_start[i])
        print(time_list_end[i])
        data=single_bad_device_warning(time_list_start[i],time_list_end[i])
        print(data)
        msg=''.join(str(data[5]).split("'"))
        sql=f"""insert into abnormal_device_table values ('{time_list_end[i]}',{data[0]},{data[1]},{data[2]},{data[4]},'{msg}')"""
        # 插入数据
        print(sql)
        result=db.execute_edit(sql)
        print(result)